/**
 * 
 */
package org.springyu.codegenerator.dao;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springyu.codegenerator.model.Column;
import org.springyu.codegenerator.model.TabStruct;
import org.springyu.codegenerator.utils.CodegenUtil;

/**
 * <p>
 * Title: 类Dao.java的实现描述：
 * </p>
 * <p>
 * Description:
 * </p>
 * <p>
 * Copyright: springyu技术有限公司 Copyright (c) 2012
 * </p>
 * <p>
 * Company: springyu技术有限公司
 * </p>
 * 
 * @author springyu 2012-8-25
 */
public class Dao {

	private Connection con;

	/**
	 * @return the con
	 */
	public Connection getCon() {
		return con;
	}

	/**
	 * @param con
	 *            the con to set
	 */
	public void setCon(Connection con) {
		this.con = con;
	}

	/**
	 * 取表注释
	 * 
	 * @param tableName
	 * @param schema
	 * @return
	 * @throws SQLException
	 */
	private Map<String, Object> getTableComments(String schema, String tableName) {

		Map<String, Object> map = new HashMap<String, Object>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String comments = "";
		StringBuilder sb = new StringBuilder(
				"select table_name,comments from all_tab_comments where owner LIKE ? and table_type = 'TABLE'");

		try {
			if (StringUtils.isNotBlank(tableName)) {
				sb.append("table_name  like ? ");
			}
			pstmt = con.prepareStatement(sb.toString());
			pstmt.setString(1, schema);
			if (StringUtils.isNotBlank(tableName)) {
				pstmt.setString(2, tableName);
			}
			rs = pstmt.executeQuery();

			while (rs.next()) {
				comments = rs.getString("comments");

				map.put(rs.getString("table_name"), comments);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}

		}

		return map;
	}

	public List<TabStruct> getTables(String catalog, String schema,
			String tablePattern) {

		System.out.println("schema=" + schema);
		Map map = getTableComments(schema, tablePattern);

		DatabaseMetaData dmd = null;
		ResultSet rs = null;
		List<TabStruct> l = new ArrayList<TabStruct>();
		try {

			dmd = con.getMetaData();
			rs = dmd.getTables(catalog, schema, tablePattern,
					new String[] { "TABLE" });

			while (rs.next()) {
				TabStruct tb = new TabStruct();
				tb.setName(rs.getString(3));
				tb.setAttrName(CodegenUtil.changeToBoName(rs.getString(3)));
				if (map.containsKey(tb.getName())) {
					String tabComm = (String) map.get(tb.getName());
					tb.setComment(tabComm);
				}

				l.add(tb);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();

			} catch (SQLException e) {

				e.printStackTrace();

			}
		}
		return l;
	}

	/**
	 * 查询表外键
	 * 
	 * @param
	 * @return List springyu 2012-8-25
	 */
	public Map<String, Object> getForeignKeyColumns(String catalog,
			String schema, String table) {

		DatabaseMetaData dmd = null;
		ResultSet rs = null;
		Map<String, Object> columns = new HashMap<String, Object>();
		try {
			dmd = con.getMetaData();
			rs = dmd.getImportedKeys(catalog, schema, table);

			while (rs.next()) {
				columns.put(rs.getString(8), table);
			}
			return columns;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return null;
	}

	/**
	 * 查询表主键
	 * 
	 * @param
	 * @return List<String> springyu 2012-8-25
	 */
	public Map<String, Object> getPrimaryKeyColumns(String catalog,
			String schema, String table) {

		DatabaseMetaData dmd = null;
		ResultSet rs = null;
		Map<String, Object> pkColumns = new HashMap<String, Object>();
		try {
			dmd = con.getMetaData();
			rs = dmd.getPrimaryKeys(catalog, schema, table);
			while (rs.next()) {
				pkColumns.put(rs.getString("COLUMN_NAME"), table);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

		return pkColumns;
	}

	public List<Column> getTableColumns(String catalog, String schema,
			String table) {
		try {
			return getTableColumns(catalog, schema, table, null);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 取列注释
	 * 
	 * @param schema
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public Map<String, Object> getColumnComments(String schema, String tableName) {

		Map<String, Object> map = new HashMap<String, Object>();

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = con
					.prepareStatement("select column_name,comments from all_col_comments where owner like ? and table_name = ? ");
			pstmt.setString(1, schema);
			pstmt.setString(2, tableName);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				String comments = rs.getString("comments");
				String column_name = rs.getString("column_name");
				map.put(column_name, comments);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

		return map;
	}

	public List<Column> getTableColumns(String catalog, String schema,
			String table, String columnPattern) throws SQLException {

		DatabaseMetaData dmd = con.getMetaData();
		ResultSet rs = null;
		// select * from all_col_comments where owner = ? and table_name = ?

		List<Column> columns = new ArrayList<Column>();
		try {
			rs = dmd.getColumns(catalog, schema, table, columnPattern);

			while (rs.next()) {
				Column aCol = new Column(rs.getString(4));
				aCol.setAttrName(CodegenUtil.changeToBoName(rs.getString(4)));
				int sqlType = rs.getShort(5);

				if (sqlType == 1111) {
					sqlType = 12345;
				}

				aCol.setSqlColumnLength(rs.getInt(7));
				aCol.setSqlDecimalLength(rs.getInt(9));
				aCol.setDataType(getJavaType(sqlType,
						aCol.getSqlColumnLength(), aCol.getSqlDecimalLength()));
				aCol.setIsNotNull(("NO".equals(rs.getString(18))));
				columns.add(aCol);
			}

		} finally {
			if (rs != null)
				rs.close();
		}
		return columns;
	}

	public Class getJavaType(int sqlType, int columnSize, int decimalDigits) {

		Class rv = String.class;

		if (sqlType == Types.CHAR || sqlType == Types.VARCHAR) {
			rv = String.class;
		} else if (sqlType == Types.FLOAT || sqlType == Types.REAL) {
			rv = Double.class;
		} else if (sqlType == Types.INTEGER) {
			rv = Integer.class;
		} else if (sqlType == Types.DOUBLE) {
			rv = Double.class;
		} else if (sqlType == 93) {

			rv = java.util.Calendar.class;
		} else if (sqlType == 12345) {

			rv = java.sql.Timestamp.class;
		} else if (sqlType == Types.TIME) {
			rv = java.util.Calendar.class;
		}
		// commented to support JDK version < 1.4
		/*
		 * else if (sqlType == Types.BOOLEAN) { rv = Boolean.class; }
		 */
		else if (sqlType == Types.SMALLINT) {
			rv = Long.class;
		} else if (sqlType == Types.BIT) {
			rv = Integer.class;
		} else if (sqlType == Types.BIGINT) {
			rv = Integer.class;
		} else if (sqlType == Types.NUMERIC || sqlType == Types.DECIMAL) {
			if (decimalDigits == 0) {
				if (columnSize < 9) {
					rv = Integer.class;
				} else {
					rv = Long.class;
				}
			} else {
				rv = Double.class;
			}
		}
		return rv;
	}

	public static void main(String a[]) throws SQLException {

		// Map map = JDBCUtil.getSchemas();
		// Iterator it = map.keySet().iterator();
		// System.out.println("map=" + map);
		// }
		// while (it.hasNext()) {
		// String catalog = (String) it.next();
		// //System.out.println("****catalog=*" + catalog);
		// List list = (List) map.get(catalog);
		// System.out.println("****list=*" + list.size());

		// List l = JDBCUtil.getTableColumns(catalog, "DB_%", "T_DJ_NSRXX");
		// System.out.println("333333table.size()=" + l.size());
		// for (int i = 0; i < l.size(); i++) {
		// Column colum = (Column) l.get(i);
		// System.out.println(colum.getJavaType());
		// }

		// for (int i=0;i<list.size() ;i++){
		// String schema=(String)list.get( i);
		// System.out.println("****schema=*"+schema);
		// List l=JDBCUtil.getTables(con,catalog,"DB_ZGXT","%");
		// // List l=JDBCUtil.getTableColumns(con,catalog,schema,"%");
		// System.out.println("333333table.size()="+l.size()) ;
		// }
		// }

		// } catch (SQLException e) {
		// TODO Auto-generated catch block
		// e.printStackTrace();
		// }
	}

}
